
from snake import Food
from snake import SnakeNode
import pymysql

# 数据库中 蛇 的信息管理
class SnakeInfo:

    def __init__(self, db, user, passwd, host='116.63.136.129', port=3306, charset='utf8'):
        """
        数据库配置
        :param db:              数据库名字
        :param user:            链接的用户名
        :param passwd:          链接的密码
        :param host:            IP地址默认是：127.0.0.1  localhost
        :param port:            端口默认：3306 可修改
        :param charset:         默认转码：utf8
        """
        self.__db = db
        self.__user = user
        self.__passwd = passwd
        self.__host = host
        self.__port = port
        self.__charset = charset
        self.__connect = None
        self.__cursor = None
    # 连接数据库
    def connect_db(self):
        """
        dbManager._connect_db()
        连接数据库
        :return:
        """
        params = {
            "db": self.__db,
            "user": self.__user,
            "passwd": self.__passwd,
            "host": self.__host,
            "port": self.__port,
            "charset": self.__charset
        }
        self.__connect = pymysql.connect(**params)
        self.__cursor = self.__connect.cursor(pymysql.cursors.DictCursor)
        # print('连接数据库成功')
    # 关闭数据库
    def Close_DB(self):
        """
        dbManager._close_db()
        :return:
        """
        self.__cursor.close()
        self.__connect.close()
        # print('关闭连接')
    # 向snake表插入数据
    def insert(self, snake_lst):
        # 打开数据库连接
        db = self.__connect
        # 使用cursor()方法获取操作游标
        cursor = self.__cursor
        # SQL 插入语句
        if len(snake_lst) == 11:
            sql = """INSERT INTO snake(head_px, head_py, tail_px, tail_py, direction, body_px, body_py
            , food_px, food_py, result_str, test_module_name) VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"""
        elif len(snake_lst) == 9:
            sql = """INSERT INTO snake(head_px, head_py, tail_px, tail_py, direction, body_px, body_py
            , result_str, test_module_name) VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s)"""
        try:
            # 执行sql语句
            cursor.execute(sql, snake_lst)
            # 提交到数据库执行
            db.commit()
        except:
            # 如果发生错误则回滚
            db.rollback()
        return db

    # 根据测试的函数名或者模块名查询snake表的所有数据
    def select_all(self, test_module_name):
        # 打开数据库连接
        db = self.__connect
        # 使用cursor()方法获取操作游标
        cursor = self.__cursor
        # SQL 查询语句
        sql = "SELECT * FROM snake WHERE test_module_name = %s"
        try:
            # 执行SQL语句
            cursor.execute(sql, test_module_name)
            # 获取所有记录列表
            results = cursor.fetchall()
            # results = results.tolist()
            return results
        except:
            print("Error: unable to fetch data")
            return None
    # 数据库中的数据处理
    def data_proc(self, results):
        if results is None:
            return None
        # 将字符串按照空格分开，转化为列表
        for i, item in enumerate(results):
            if 'body_px' in item and 'body_py' in item:
                results[i]['body_px'] = self.str_to_lst(item['body_px'])
                results[i]['body_py'] = self.str_to_lst(item['body_py'])
            if 'food_px' in item and 'food_py' in item and item['food_px'] is not None and item['food_py'] is not None:
                results[i]['food_px'] = self.str_to_lst(item['food_px'])
                results[i]['food_py'] = self.str_to_lst(item['food_py'])
            if ' ' in item['result_str']:
                results[i]['result_str'] = self.str_to_lst(item['result_str'])
            else:
                results[i]['result'] = results[i]['result_str']
        return results
    # 字符串转为列表数字
    def str_to_lst(self, str):
        numbers = str.split()
        numbers = [int(x) for x in numbers]
        return numbers



if __name__ == '__main__':
    snakes = SnakeInfo('software_test', 'root', 'Root@1234')
    snakes.connect_db()
    snakes.insert('')

    results = snakes.select_all('test_forward_head')
    for item in (snakes.data_proc(results)):
        print(item)
    snakes.Close_DB()